//api服务(接口)
var express = require('express');
var bodyParser = require('body-parser');
var app = express();
app.use(bodyParser.json());
var port = 3008;
var DB = require('./sql');
app.get('/',function(req,res){
    res.send('welcome to api service');
    res.end();
});

//测试接口-获取记录
app.post('/service/getTest',function(req,res){
    //如果表不存在，建表
    var db = DB();
    db.all("SELECT id, info,datetime FROM test", function(err, rows) {
        if(err){
            console.log('查询表出错');
            return;
        }
        var list = [];
        rows.forEach(function (row) {
            //console.log(row.id + ": " + row.info);
            list.push({id:row.id,text:row.info,date:row.datetime});
        });
        db.close();
        res.json({success:true,list:list});
        res.end();
    });
});
//测试接口-新增记录
app.post('/service/addTest',function(req,res){
    var db = DB();
    var info = req.body.info;
    var stmt = db.prepare("INSERT INTO test VALUES (?,?,?)");
    stmt.run(null,info,Date.now());
    stmt.finalize(function(){
        res.json({success:true,replyText:"新增test表的记录成功！"});
        res.end();
    });
    db.close();
});
//测试接口-删除记录
app.post('/service/deleteTest',function(req,res){
    var db = DB();
    var id = req.body.id;
    db.get('SELECT * FROM test where id='+id,function(err,row){
        if(!row){
            res.json({success:false,replyText:'记录不存在或者已经被删除'});
            res.end();
            db.close();
        }
        db.run("DELETE FROM test WHERE id="+id,function(err,rows){
            console.log(err);
            res.json({success:true,replyText:"删除test表 记录成功！"});
            res.end();
            db.close();
        });
    })
});

/**
 * 省查询接口
 */
app.post('/service/getProvince',function(req,res){
    var db = DB();
    db.all("SELECT * FROM t_province", function(err, rows) {
        if(err){
            res.json({success:false,replyText:err.toString()});
            res.end();
        }
        var list = [];
        rows.forEach(function (row) {
            list.push({id:row.provinceId,provinceName:row.provinceName});
        });
        db.close();
        res.json({success:true,list:list});
        res.end();
    });
});

/**
 * 市查询接口
 * 传参 provinceId
 */
app.post('/service/getCity',function(req,res){
    var provinceId = req.body.provinceId;
    var db = DB();
    db.all("SELECT * FROM t_city where provinceId ="+provinceId, function(err, rows) {
        if(err){
            res.json({success:false,replyText:err.toString()});
            res.end();
        }
        var list = [];
        rows.forEach(function (row) {
            list.push({id:row.cityId,cityName:row.cityName});
        });
        db.close();
        res.json({success:true,list:list});
        res.end();
    });
});

/**
 * 区县查询接口
 * 传参 cityId
 */
app.post('/service/getZone',function(req,res){
    var cityId = req.body.cityId;
    var db = DB();
    db.all("SELECT * FROM t_zone where cityId ="+cityId, function(err, rows) {
        if(err){
            res.json({success:false,replyText:err.toString()});
            res.end();
        }
        var list = [];
        rows.forEach(function (row) {
            list.push({id:row.zoneId,zoneName:row.zoneName});
        });
        db.close();
        res.json({success:true,list:list});
        res.end();
    });
});

/**
 * 通讯录列表分页查询
 * 传参 
 * { pageNumber, pageSize,}
 */
app.post('/service/getContactList',function(req,res){
    var pageNumber = req.body.pageNumber || 1;
    var pageSize = req.body.pageSize || 10;
    var offsetNum = (pageNumber-1) * pageSize;
    var db = DB();
    db.all("SELECT count(*) as recordCount FROM t_contact", function(err, rows) {
        if(err){
            res.json({success:false,list:[],replyText:err.toString()});
            res.end();
        }else{
            console.log(rows);
            var recordCount = (rows[0]||{}).recordCount;
            db.all("SELECT id as contactId,name,age,gender FROM t_contact ORDER BY update_date DESC LIMIT "+pageSize+" OFFSET "+offsetNum, function(err, rows) {
                if(err){
                    res.json({success:false,list:[],replyText:err.toString()});
                    res.end();
                }
                console.log(rows);
                res.json({success:true,list:rows,pager:{recordCount:recordCount,pageNumber:pageNumber,pageSize:pageSize}});
                res.end();
            })
        }
        db.close();
    });
});
/**
 * 联系人详情查询
 * 传参 
 * { id }
 */
app.post('/service/getContactDetail',function(req,res){
    var contactId = req.body.contactId;
    var db = DB();
    if(!contactId){
        res.json({success:false,replyText:"id和姓名必须传"});
        res.end();
        return db.close();
    }
    db.all("SELECT * FROM t_contact WHERE id ="+contactId, function(err, rows) {
        if(!err){
            res.json({success:true,detail:rows[0]});
            res.end();
        }else{
            res.json({success:false,replyText:err.toString()});
            res.end();
        }
        db.close();
    });
});
/**
 * 通讯录记录添加
 * 传参 
 * { name, age, gender, desc }
 */
app.post('/service/addContact',function(req,res){
    var name = req.body.name;
    var age = req.body.age;
    var gender = req.body.gender;
    var desc = req.body.desc;
    var db = DB();
    var stmt = db.prepare("INSERT INTO t_contact VALUES (?,?,?,?,?,?,?)");
    stmt.run(null,name,age,gender,desc,Date.now(),Date.now());
    stmt.finalize(function(err,row){
        db.all('SELECT last_insert_rowid() as newid',function(err,results){
            console.log(err,row);
            res.json({success:true,replyText:"新增通讯录成功！",contactId:results[0].newid});
            res.end();
        })
    });
    db.close();
});

/**
 * 通讯录记录编辑
 * 传参 
 * { id, name, age, gender, desc }
 */
app.post('/service/editContact',function(req,res){
    var id = req.body.contactId;
    var name = req.body.name;
    var age = req.body.age;
    var gender = req.body.gender;
    var desc = req.body.desc;
    var db = DB();
    if(!id || !name){
        res.json({success:false,replyText:"id和姓名必须传"});
        res.end();
        return db.close();
    }
    //db.run("UPDATE t_contact SET name="+name+", age="+age+", gender="+gender+", desc="+desc+" WHERE id= "+id,);
    db.run("UPDATE t_contact SET name=?, age=?, gender=?, desc=?, update_date=? WHERE id=?",[name,age,gender,desc,Date.now(),id],function(err,row){
        res.json({success:!err,replyText:!err?"编辑通讯录成功！":"编辑失败！"});
        res.end();
    });
    db.close();
});

/**
 * 删除通讯录记录
 * 传参 
 * { id }
 */
app.post('/service/deleteContact',function(req,res){
    var id = req.body.id;
    var db = DB();
    if(!id){
        res.json({success:false,replyText:"id和姓名必须传"});
        res.end();
        return db.close();
    }
    db.run("DELETE FROM t_contact WHERE id=?",[id],function(err,row){
        res.json({success:!err,replyText:!err?"删除通讯录成功！":"删除失败！"});
        res.end();
    });
    db.close();
});




app.use('*',function(req,res){
    res.status(404);
    res.json({success:false,replyText:'没有这个接口'})
});
app.listen(port,function(){
    console.log('api service is running at port: '+port)
});